#!/usr/bin/perl

use strict;
use DBI;
use CGI;

my $dsn = 'DBI:SQLite:dbname=../oneseg24/epg.db';
my $dbh = DBI->connect($dsn, undef, undef, {RaiseError => 1, AutoCommit => 0});

my @tm = localtime(time - 60*60*4); $tm[5] += 1900; $tm[4]++;
my $q = new CGI;
my $year = $q->param('year');
my $month = $q->param('month');
my $day = $q->param('day');

if (!$year || !$month || !$day) {
	$year = $tm[5];
	$month = $tm[4];
	$day = $tm[3];
}

my $date = sprintf("%04d-%02d-%02d", $year, $month, $day);

my @textcolors = (
	'#999999',
	'#ffffff',
	'#999999',
);

my @bgcolors = (
	'#333333',
	'#666666',
	'#553333',
);

print "Content-type: text/html\n\n";

print <<END;
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=utf-8">
<title>OneSeg24 - timetable</title>
</head>
<body bgcolor="#000000" text="#ffffff" link="#ffff00" vlink="#ffff00">
<form action="$ENV{SCRIPT_NAME}" method="get">
<select name="year">
END

for (my $i = $tm[5] - 1; $i <= $tm[5]; $i++) {
	if ($i == $year) {
		print "<option value=\"$i\" selected>$i</option>\n";
	} else {
		print "<option value=\"$i\">$i</option>\n";
	}
}

print <<END;
</select> 年
<select name="month">
END

for (my $i = 1; $i <= 12; $i++) {
	if ($i == $month) {
		print "<option value=\"$i\" selected>$i</option>\n";
	} else {
		print "<option value=\"$i\">$i</option>\n";
	}
}

print <<END;
</select> 月
<select name="day">
END

for (my $i = 1; $i <= 31; $i++) {
	if ($i == $day) {
		print "<option value=\"$i\" selected>$i</option>\n";
	} else {
		print "<option value=\"$i\">$i</option>\n";
	}
}

print <<END;
</select> 日
<input type="submit" value="表示">
</form>
<center>
<table>

<tr bgcolor="#666688">
<td><br></td>
END

my $sql = <<END;
select
	dc_id,
	dc_name
from
	da_channel
order by
	dc_id
END

my $sth = $dbh->prepare($sql);
my $rv = $sth->execute();

my @channel = ();

while (my $ref = $sth->fetch()) {
	my($dc_id, $dc_name) = @$ref;

	push(@channel, $dc_id);

	print "<th>$dc_name</th>";
}

print <<END;
<td><br></td>
</tr>
END

my $sql = <<END;
select
	p.dv_id,
	p.dv_name,
	p.dv_text,

	(select
		g1.mg_name
	from
		da_genre g
			left outer join
				ma_genre1 g1
			on
				g1.mg_level1 = g.mg_level1
	where
		g.dv_id = p.dv_id
	order by
		g.dg_id
	limit
		1
	),

	c.dc_id,
	c.dc_ch,

	strftime('%s', p.dv_start_time),
	strftime('%s', datetime(p.dv_start_time, p.dv_duration)),
	strftime('%Y%m%d-%H%M%S', p.dv_start_time),
	strftime('%H%M%S', datetime(p.dv_start_time, p.dv_duration)),
	strftime('%H', p.dv_start_time),
	strftime('%M', p.dv_start_time),

	julianday(p.dv_start_time) - julianday(?),
	p.dv_start_time <= datetime('now', 'localtime'),
	datetime('now', 'localtime') < datetime(p.dv_start_time, p.dv_duration)

from
	da_event p
		inner join
			da_channel c
		on
			c.dc_network_id = p.dv_original_network_id
		and
			c.dc_service_id = p.dv_service_id
where
	c.dc_id = ?
and
	datetime(?, '4 hours') <= p.dv_start_time
and
	p.dv_start_time < datetime(?, '29 hours')
order by
	p.dv_start_time
END

my $sth = $dbh->prepare($sql);

my $program = {};

foreach my $dc_id (@channel) {

	my $rv = $sth->execute($date, $dc_id, $date, $date);

	my $oHour = 4;
	my $oMinute = 0;

	while (my $ref = $sth->fetch()) {
		my(
			$dp_id,
			$dp_title,
			$dp_desc,
			$dt_name,
			$dc_id,
			$dc_ch,
			$start_epoch,
			$end_epoch,
			$start,
			$end,
			$hour,
			$minute,
			$days,
			$onair,
			$recording,
		) = @$ref;

		$hour = int($hour + int($days) * 24);
		$minute = int($minute);
		my $duration = int($end_epoch / 60) - int($start_epoch / 60);

		&dummy_program(\$oHour, \$oMinute, $hour, $minute, $dc_id);

		$program->{$dc_id}->{$hour}->{$minute} = [
			$dp_id,
			$dp_title,
			$dp_desc,
			$dt_name,
			$dc_id,
			$dc_ch,
			$start,
			$end,
			$hour,
			$minute,
			$duration,
			$onair,
			$recording,
		];

		$oMinute += $duration;

		while (60 <= $oMinute) {
			$oMinute -= 60;
			$oHour++;
		}
	}

	my $hour = 29;
	my $minute = 0;

	&dummy_program(\$oHour, \$oMinute, $hour, $minute, $dc_id);
}

sub dummy_program {
	my($oHour, $oMinute, $hour, $minute, $dc_id) = @_;

	if ($$oHour != $hour || $$oMinute != $minute) {
		my $tHour = $hour;
		my $tMinute = $minute;

		my $oDuration = $tMinute - $$oMinute;

		while ($oDuration < 0) {
			$oDuration += 60;
			$tHour--;

			if ($tHour < 0) {
				$tHour += 24;
			}
		}

		my $pHour = $tHour - $$oHour;

		while ($pHour < 0) {
			$pHour += 24;
		}

		$oDuration += 60 * $pHour;

		$program->{$dc_id}->{$$oHour}->{$$oMinute} = [
			undef,
			undef,
			undef,
			undef,
			undef,
			undef,
			undef,
			undef,
			undef,
			undef,
			$oDuration,
			undef,
			undef,
		];

		$$oMinute += $oDuration;

		while (60 <= $$oMinute) {
			$$oMinute -= 60;
			$$oHour++;
		}
	}
}

for (my $hour = 4; $hour <= 28; $hour++) {

	for (my $minute = 0; $minute < 60; $minute++) {
		print "<tr>\n";

		if ($minute == 0) {
			printf "<th rowspan=\"60\" bgcolor=\"#666688\">%d</th>\n", $hour % 24;
		}

		foreach my $dc_id (@channel) {
			if (defined $program->{$dc_id}->{$hour}->{$minute}) {
				my(
					$dp_id,
					$dp_title,
					$dp_desc,
					$dt_name,
					$dc_id,
					$dc_ch,
					$start,
					$end,
					$hour,
					$minute,
					$duration,
					$onair,
					$recording,
				) = @{$program->{$dc_id}->{$hour}->{$minute}};

				if (!$dp_id) {
					print "<td rowspan=\"$duration\" bgcolor=\"$bgcolors[0]\"><br></td>\n";
					next;
				}

				my $rs_id = 0;
				my $start_time = sprintf('%d:%02d', $hour % 24, $minute);

				if ($onair) {
					if ($recording) {
						$rs_id = 2;
					} else {
						$rs_id = 1;
					}

					my $link = join('-', substr($start, 2), $end, $dc_ch);
					$dp_title = "<a href=\"player.cgi?$link\">$dp_title</a>";
				}

				my $desc = '';

				if ($dp_desc ne '') {
					$desc .= "<br>$dp_desc";
				}

				if ($dt_name ne '') {
					$desc .= "<div align=\"right\">($dt_name)</div>";
				}

				print <<END;
<td valign="top" rowspan="$duration" bgcolor="$bgcolors[$rs_id]"><font color="$textcolors[$rs_id]">$start_time<br>
$dp_title$desc</font></td>
END
			}
		}

		if ($minute == 0) {
			printf "<th rowspan=\"60\" bgcolor=\"#666688\">%d</th>\n", $hour % 24;
		}

		print "</tr>\n";
	}
}

print <<END;
</table>
</center>
</body>
</html>
END

$dbh->disconnect();

